package com.UserModule.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import util.ConnectDB;
import util.Constant;
import util.UtilDAO;

import com.UserModule.bean.TbUserBean;

/**
 * user operate DAO
 * @author vsked
 * 
 */
public class TbUserDAO {
	private Connection conn = null;
	private PreparedStatement pt = null;
	private ResultSet rs = null;
	private boolean flag = false;
	private String sql = "";
	
	private TbUserBean tbUsers=null;
	private List<TbUserBean> tbUsersList=null;
	
	/**
	 * add user
	 * @param inTbUsers
	 * @return
	 */
	public boolean add(TbUserBean inTbUsers){
        try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "insert into tb_User(userName,userPass,createTime,orgNum) values(?,?,?,?)";
            pt = conn.prepareStatement(sql);
            pt.setString(1, inTbUsers.getUserName());
            pt.setString(2, inTbUsers.getUserPass());
            pt.setString(3, Constant.decodeDate());
            pt.setInt(4, inTbUsers.getOrgNum());
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
		return flag;
	}
	
	/**
	 * delete user
	 * @param inUserId
	 * @return
	 */
	public boolean del(int inUserId){
		Connection inConn=null;
		try{
		inConn=ConnectDB.getSqlServerConnection();
		UtilDAO.del("tb_User", "userId", inUserId);	
		flag=true;
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			ConnectDB.closeSqlConnection();
		}
		return flag;
			
	}
	
	public boolean edit(TbUserBean inTbUsers){
		try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "update tb_User set userName=?,userPass=?  where userId=?";
            pt = conn.prepareStatement(sql);
            pt.setString(1, inTbUsers.getUserName());
            pt.setString(2, inTbUsers.getUserPass());
            pt.setInt(3, inTbUsers.getUserId());
            if (pt.executeUpdate() > 0) {
                flag = true;
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectDB.closeSqlConnection();
        }
		return flag;
	}
	
	/**
	 * get tbusers by userid
	 * @param inUserId
	 * @return
	 */
	public TbUserBean getTbUsersByUserId(int inUserId){
		try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from tb_User where userId=?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, inUserId);
            rs = pt.executeQuery();
            if (rs.next()) {
               tbUsers=this.getTbUsersInfo(rs);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
           ConnectDB.closeSqlConnection();
        }
		return tbUsers;
	}
	
	/**
	 * login check method
	 * @param inName username
	 * @param inPassword password
	 * @return
	 */
	public TbUserBean checkLogin(String inName, String inPassword) {
        try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from tb_User where userName=? and userPass=?";
            pt = conn.prepareStatement(sql);
            pt.setString(1, inName);
            pt.setString(2, inPassword);
            rs = pt.executeQuery();
            if (rs.next()) {
               tbUsers=this.getTbUsersInfo(rs);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
           ConnectDB.closeSqlConnection();
        }
		return tbUsers;
	}
	
	 
	public List<TbUserBean> getAllTbUsers(){
        try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from tb_User";
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            while (rs.next()) {
                   if(tbUsersList==null){
                	   tbUsersList=new ArrayList<TbUserBean>();
                   }
                   tbUsersList.add(this.getTbUsersInfo(rs));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
           ConnectDB.closeSqlConnection();
        }
		return tbUsersList;
	}
	
	 
	/**
	 * input ResultSet get TbUserBean Info
	 * @param rs ResultSet
	 * @return TbUserBean
	 * @throws Exception
	 */
	public TbUserBean getTbUsersInfo(ResultSet rs) throws Exception{
		TbUserBean inTbUsers=new TbUserBean();
		inTbUsers.setUserId(rs.getInt("userId"));
		inTbUsers.setUserName(rs.getString("userName"));
		inTbUsers.setUserPass(rs.getString("userPass"));
		inTbUsers.setCreateTime(rs.getString("createTime"));
		inTbUsers.setOrgNum(rs.getInt("orgNum"));
		return inTbUsers;				
	}
	
	/**
	 * get database exist userOrgNumArray
	 * @return List<Integer> or null
	 */
	public List<Integer> getUserOrgNumArray(){
		List<Integer> orgNumList=null;
		try {
            conn = ConnectDB.getSqlServerConnection();
            sql = "select * from tb_User";
            pt = conn.prepareStatement(sql);
            rs = pt.executeQuery();
            while (rs.next()) {
                   if(orgNumList==null){
                	   orgNumList=new ArrayList<Integer>();
                   }
                   orgNumList.add(rs.getInt("orgNum"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
           ConnectDB.closeSqlConnection();
        }		
		return orgNumList;		
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		/*
		TbUserBean tu=new TbUsersDAO().checkLogin("admin", "admin");
		System.out.println(tu);
		*/
		
		List<TbUserBean> tuList=new TbUserDAO().getAllTbUsers();
		if(tuList!=null){
			for(int i=0;i<tuList.size();i++){
				TbUserBean tu=tuList.get(i);
			}
		}
		
	}//end main method

}//end class
